package task;

import java.io.FileWriter;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.Set;

import util.xml.Builder;
import util.xml.Builder.Attr;


public class DbContent {
	
	private String schema;
	private Connection conn;
	private String[] tables = null;
	public DbContent(Connection conn, String schema) {
		this.conn = conn;
		this.schema = schema;
	}
	public void setConnection(Connection conn) {
		this.conn = conn;
	}
	public void dumpContent(PrintWriter os, Set<String> exclude) throws SQLException {
		if (exclude == null) {
			exclude = new HashSet<String>();
		}
		populateTableNames();
		for (String name : tables) {
			report(os, name, exclude.contains(name));
		}
	}
	private void report(PrintWriter os, String name, boolean exclude) throws SQLException {
		os.println("=================================================");
		os.printf("Table:    %s :%n", name);
		os.println("=================================================");
		String sql = String.format("select * from %s.%s", schema, name);
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		ResultSetMetaData meta = rs.getMetaData();
		int n = meta.getColumnCount();
		for (int i=1 ; i<=n ; i++) {
			os.printf("<%s> ", meta.getColumnName(i));
		}
		os.println();
		if (!exclude) {
			while (rs.next()) {
				for (int i=1 ; i<=n ; i++) {
					String value = rs.getString(i);
					os.printf("%s ", value);
				}
				os.println();
			}
		}
		rs.close();
		stmt.close();
	}
	public void dumpHtmlContent(PrintWriter os, Set<String> exclude) throws SQLException {
		if (exclude == null) {
			exclude = Collections.emptySet();
		}
		populateTableNames();
		Builder b = new Builder(os);
		makeTableList(b, tables);
		for (String name : tables) {
			//reportHtml(b, name, true);
			reportHtml(b, name, exclude.contains(name.toUpperCase()));
		}
		postProcess(b);
	}
	private void reportHtml(Builder b, String name, boolean exclude) throws SQLException {
		b.write("a", null, new Attr("name",name));
		b.write("h2", String.format("Table:    %s :", name));
		String sql = String.format("select * from %s.%s", schema, name);
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		ResultSetMetaData meta = rs.getMetaData();
		int n = meta.getColumnCount();
		b.begin("table", new Attr("border","1"), new Attr("bgcolor", "#FFFFBB"));
		b.begin("tr", new Attr("bgcolor", "#DDFFDD"));
		for (int i=1 ; i<=n ; i++) {
			b.begin("th").write(meta.getColumnName(i).toLowerCase() + ":" 
					+ meta.getColumnTypeName(i) + ":" + meta.getColumnDisplaySize(i)).end();
		}
		b.end(); //tr
		
		if (!exclude) {
			while (rs.next()) {
				b.begin("tr");
				for (int i=1 ; i<=n ; i++) {
					String value = rs.getString(i);
					b.write("td", value);
				}
				b.end(); //tr
			}
		} else {
			rs.close();
			stmt.close();
			sql = String.format("select count(*) from %s.%s", schema, name);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			rs.next();
			String value = rs.getString(1);
			b.begin("tr").write("td", value + " rows", new Attr("colspan", "" + n)).end();
		}
		b.end();//table
		b.write("a", "To top", new Attr("href", "#__top__"));
		rs.close();
		stmt.close();
	}

	
	
	private void postProcess(Builder b) {
		b.end().end();
		b.flush();
	}
	private void makeTableList(Builder b, String[] tables) {
		b.begin("html");
		b.begin("head");
		b.write("title","Table Content");
		b.end();
		b.begin("body");
		b.write("a", null, new Attr("name","__top__"));
		b.write("h2", "Tables").begin("ul");
		for (String table : tables) {
			b.begin("li");
			b.write("a", table, new Attr("href", "#" + table));
			b.end();
		}
		b.end();
	}
	private void populateTableNames() throws SQLException {
		if (tables != null) return;
		DatabaseMetaData meta = conn.getMetaData();
		String driverName = meta.getDriverName();
		if (driverName.toLowerCase().contains("oracle")) {
			populateTableNamesOracle();
			return;
		}
		String[] types = { "TABLE" };
		ResultSet rs = meta.getTables(null, schema, null, types);
//		dump(rs, new HashSet<String>());
		ArrayList<String> list = new ArrayList<String>();  
		while (rs.next()) {
			String name = rs.getString("table_name");
			list.add(name);
		}
		rs.close();
		tables = list.toArray(new String[0]);
	}
	private void dump(ResultSet rs, HashSet<String> set) throws SQLException {
		ResultSetMetaData meta = rs.getMetaData();
		int n = meta.getColumnCount();
		for (int i=1 ; i<=n ; i++) {
			System.out.printf("c:%s ", meta.getColumnName(i));
		}
		System.out.printf("%n");
		int count = -10;
		while (rs.next()) {
			for (int i=1 ; i<=n ; i++) {
				String value = rs.getString(i);
				System.out.printf("'%s' ", value);
				if (i==2) {
					set.add(value);
				}
			}
			System.out.printf("%n");
			if (count-- == 0) break;
		}
	}
	private void populateTableNamesOracle() throws SQLException {
		if (tables != null) return;
		Statement stmt = conn.createStatement();
		String sql = String.format("SELECT TABLE_NAME from ALL_TABLES where OWNER='%s'", schema);
		ResultSet rs = stmt.executeQuery(sql);
		ArrayList<String> list = new ArrayList<String>();  
		while (rs.next()) {
			String name = rs.getString("TABLE_NAME");
			list.add(name);
		}
		rs.close();
		stmt.close();
		tables = list.toArray(new String[0]);
	}
	
	/**
	 * @param args
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 * @throws IOException 
	 */
	public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
		String[] schemas = { 
				//"netmaster", 
//				"fmj_r10j", 
//				"fmj_r10k_0", 
//				"FMJ_R10K00_1130", 
				"FMJ_J2K", 
//				"FMJ_R10J" 
				};
		for (String schema : schemas) {
			process(schema);
		}
	}
	private static void process(String schema) throws SQLException, ClassNotFoundException, IOException {
		boolean oracle = schema.equals(schema.toUpperCase());
		Connection con = oracle ? getOracleConnection() : getPGConnection();
		DbContent content = new DbContent(con, schema);
		File file = new File(String.format("DbContent-%s-%s.html", schema, oracle?"o":"p"));
		PrintWriter os = new PrintWriter(new FileWriter(file));
		HashSet<String> exclude = new HashSet<String>();
		exclude.add("META_MODEL");
//		exclude.add("MED_DISCOVERED_ELEMENTS");
		exclude.add("CORE_SECURITY_ACTION");
		exclude.add("CORE_SECURITY_AUDIT");
		content.dumpHtmlContent(os, exclude);
		os.close();
		//if (!oracle) content.test();
		con.close();
		System.out.printf("Content of tables in schema:%s is written to %s%n", 
				content.getSchema(), file.getCanonicalPath());
	}
	private void test() throws SQLException {
		DatabaseMetaData meta = conn.getMetaData();
		for (String table : tables) {
			ResultSet rs = meta.getIndexInfo(null, schema, table, false, true);
			System.out.printf("Table:  --- %s ---%n", table);
			dump(rs, new HashSet<String>());
			rs.close();
		}
	}
	private Object getSchema() {
		return schema;
	}
	
	static Connection getPGConnection() throws SQLException, ClassNotFoundException {
		Class.forName("org.postgresql.Driver");
		Connection con = DriverManager.getConnection
		//("jdbc:postgresql://nmssql:5432/pg_utf8", "postgres", "adminpasswd");
		("jdbc:postgresql://VM-FMJ2:5432/postgres", "postgres", "adminpasswd");
		return con;
	}
	static Connection getOracleConnection() throws SQLException, ClassNotFoundException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection
        ( "jdbc:oracle:thin:@VM-FMJ4:1521:ora", "system","adminpasswd");
//        ( "jdbc:oracle:thin:@nmsora:1521:nmsora", "system","adminpasswd");
		return con;
	}

}
